package com.mini.mysql.repository;

import com.mini.mysql.model.BusinessStudentInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author anjl
 * @date 2019/10/2713:54
 */
@Repository
public interface BusinessStudentInfoRepository extends JpaRepository<BusinessStudentInfo, String>, JpaSpecificationExecutor<BusinessStudentInfo> {

    String countQuery = "select 50000000 \n" +
            "from\n" +
            "        business_student_info o \n" +
            "    left join business_student_info_attach a\n" +
            "         on o.order_no=a.order_no \n" +
            "    left join business_teacher_info t \n" +
            "         on o.teacher_id=t.teacher_id  \n" +
            "where \n" +
            "" +
            "o.update_time between ?1 and ?2 \n" +
            " limit 1";

    String sqlQuery = "select " +
            "o.phone_no,\n" +
            "o.order_no,\n" +
            "t.group_id,\n" +
            "o.add_wechat_time,\n" +
            "o.add_wechat_group_state,\n" +
            "o.admission_test_time,\n" +
            "a.big_lesson_num_this_week,\n" +
            "a.big_lesson_num_other_week,\n" +
            "a.order_expense,\n" +
            "o.order_price,\n" +
            "a.order_cost,\n" +
            "a.big_lesson_flow_this_week,\n" +
            "a.big_lesson_flow_other_week,\n" +
            "a.big_lesson_refund_num,\n" +
            "a.big_lesson_refund_amount,\n" +
            "o.update_time as order_update_time,\n" +
            "a.update_time as attach_update_time,\n" +
            "t.update_time as teacher_update_time,\n" +
            "c.update_time as class_update_time, \n" +
            "b.update_time as base_update_time, \n" +
            "o.create_time, \n" +
            "o.teacher_id,\n" +
            "o.business_type, \n" +
            "o.lesson_id, \n" +
            "o.lesson_title, \n" +
            "o.wechat_name_note, \n" +
            "o.wechat_name, \n" +
            "o.wx_id, \n" +
            "o.user_id, \n" +
            "o.nick_name, \n" +
            "o.fz_id, \n" +
            "o.fz_name, \n" +

            "o.get_coupon, \n" +
            "o.get_coupon_time, \n" +
            "o.follow_public, \n" +
            "o.follow_public_time, \n" +
            "o.un_pay_zero_state, \n" +
            "o.states, \n" +
            "o.order_pay_time, \n" +
            "c.order_no as class_order_no, \n" +
            "c.attn_count, \n" +
            "c.attn_live_count, \n" +
            "c.attn_vod_count, \n" +
            "c.finish_live_count, \n" +
            "c.finish_count, \n" +
            "c.class_course_count, \n" +
            "c.duration_live_count, \n" +
            "c.duration_count, \n" +
            "c.class_course_duration, \n" +
            "s.change_class_state, \n" +
            "o.mini_lesson_id, \n" +
            "b.work_room_id, \n" +
            "b.work_room_name, \n" +
            "b.channel_code, \n" +
            "b.channel_name \n" +
            "from\n" +
            "        business_student_info o \n" +
            "    left join business_student_info_attach a\n" +
            "         on o.order_no=a.order_no \n" +
            "    left join business_teacher_info t \n" +
            "         on o.teacher_id=t.teacher_id  \n" +
            "    left join attn_class_student_count c \n" +
            "         on c.order_no=o.order_no  \n" +
            "    left join attn_student_order s \n" +
            "         on s.order_no=o.order_no  \n" +
            "    left join business_base_info b \n" +
            "         on b.mini_lesson_id = o.mini_lesson_id \n" +
            "where \n" +
            "";

    @Query(value = "select count(*) from business_student_info o\n" +
            "\n" +
            "where o.update_time BETWEEN ?1 and ?2", nativeQuery = true)
    Integer count(Date startTime, Date endTime);

    @Query(value = sqlQuery +
            "o.update_time between ?1 and ?2 \n"
            ,
            countQuery = countQuery,
            nativeQuery = true)
    Page<Map<String, Object>> page(Date orderStartTime, Date orderEndTime, Pageable pageable);

    @Query(value = "select count(*) from business_student_info_attach o\n" +
            "\n" +
            "where o.update_time BETWEEN ?1 and ?2", nativeQuery = true)
    Integer countAttach(Date startTime, Date endTime);

    @Query(value = "select count(*) from  attn_class_student_count o\n" +
            "\n" +
            "where o.update_time BETWEEN ?1 and ?2", nativeQuery = true)
    Integer countClass(Date startTime, Date endTime);

    @Query(value = sqlQuery +
            "a.update_time between ?1 and ?2 order by a.update_time asc\n"
            ,
            countQuery = countQuery,
            nativeQuery = true)
    Page<Map<String, Object>> pageAttach(Date orderStartTime, Date orderEndTime, Pageable pageable);


    @Query(value = sqlQuery +
            "c.update_time between ?1 and ?2 order by c.update_time asc\n"
            ,
            countQuery = countQuery,
            nativeQuery = true)
    Page<Map<String, Object>> pageClass(Date orderStartTime, Date orderEndTime, Pageable pageable);

    Integer countByOrderPayTimeBetween(Date startTime, Date endTime);

    @Query(value = "select new com.mini.mysql.model.BusinessStudentInfo(o.orderNo) from com.mini.mysql.model.BusinessStudentInfo o where o.orderPayTime between ?1 and ?2 ")
    List<BusinessStudentInfo> findOrderNo(Date startTime, Date endTime);



}
